﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Text;
using Framework;

namespace Enterprise
{
    public class CandidatesService : BaseService
    {
        public int Add(string post, string workExperience, string education, string age,
                                      DateTime date, string language, string email, string name,
                                     string mobile, string qq, string phone, string summary, string identNumber,
                                      string workHistory, string sex, bool isRead)
        {
            Check.Argument.IsNotOutOfLength(post, 25, true, "应聘岗位");
            Check.Argument.IsNotOutOfLength(name, 25, true, "姓名");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("insert into TbCandidates(");
            strSqlOne.Append("CoPost,CoIsRead,CoWorkExperience,CoEducation,CoAge,CoDate,CoLanguage,CoEmail,CoName,CoMobile,CoQq,CoPhone,CoSummary,CoIdentNumber,CoWorkHistory,CoSex");
            strSqlOne.Append(")");
            strSqlOne.Append(" values (");
            strSqlOne.Append("@CoPost,@CoIsRead,@CoWorkExperience,@CoEducation,@CoAge,@CoDate,@CoLanguage,@CoEmail,@CoName,@CoMobile,@CoQq,@CoPhone,@CoSummary,@CoIdentNumber,@CoWorkHistory,@CoSex");
            strSqlOne.Append(");");
            strSqlOne.Append("select last_insert_rowid() from TbCandidates;");
            SQLiteParameter[] parametersOne =
                {
                    new SQLiteParameter("@CoPost", DbType.String, 255),
                    new SQLiteParameter("@CoWorkExperience", DbType.String),
                    new SQLiteParameter("@CoEducation", DbType.String, 255),
                    new SQLiteParameter("@CoAge", DbType.String),
                    new SQLiteParameter("@CoDate", DbType.DateTime),
                    new SQLiteParameter("@CoLanguage", DbType.String, 255),
                    new SQLiteParameter("@CoEmail", DbType.String, 255),
                    new SQLiteParameter("@CoName", DbType.String, 255),
                    new SQLiteParameter("@CoMobile", DbType.String, 255),
                    new SQLiteParameter("@CoQq", DbType.String, 255),
                    new SQLiteParameter("@CoPhone", DbType.String, 255),
                    new SQLiteParameter("@CoSummary", DbType.String),
                    new SQLiteParameter("@CoIdentNumber", DbType.String, 255),
                    new SQLiteParameter("@CoWorkHistory", DbType.String),
                    new SQLiteParameter("@CoSex", DbType.String,5),
                    new SQLiteParameter("@CoIsRead", DbType.Boolean)
                };
            parametersOne[0].Value = post;
            parametersOne[1].Value = workExperience;
            parametersOne[2].Value = education;
            parametersOne[3].Value = age;
            parametersOne[4].Value = date;
            parametersOne[5].Value = language;
            parametersOne[6].Value = email;
            parametersOne[7].Value = name;
            parametersOne[8].Value = mobile;
            parametersOne[9].Value = qq;
            parametersOne[10].Value = phone;
            parametersOne[11].Value = summary;
            parametersOne[12].Value = identNumber;
            parametersOne[13].Value = workHistory;
            parametersOne[14].Value = sex;
            parametersOne[15].Value = isRead;
            var obj = DbHelperSqLite.GetSingle(strSqlOne.ToString(), parametersOne).SafeToInt32();
            return obj;
        }
        public int Edit(int id, string post, string workExperience, string education, string age,
                                      DateTime date, string language, string email, string name, string mobile, string qq, string phone, string summary, string identNumber,
                                      string workHistory, string sex)
        {
            Check.Argument.IsNotOutOfLength(post, 25, true, "应聘岗位");
            Check.Argument.IsNotOutOfLength(name, 25, true, "姓名");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("update TbCandidates set ");
            strSqlOne.Append("CoPost=@CoPost");
            strSqlOne.Append(",");
            strSqlOne.Append("CoWorkExperience=@CoWorkExperience");
            strSqlOne.Append(",");
            strSqlOne.Append("CoEducation=@CoEducation");
            strSqlOne.Append(",");
            strSqlOne.Append("CoAge=@CoAge");
            strSqlOne.Append(",");
            strSqlOne.Append("CoDate=@CoDate");
            strSqlOne.Append(",");
            strSqlOne.Append("CoLanguage=@CoLanguage");
            strSqlOne.Append(",");
            strSqlOne.Append("CoEmail=@CoEmail");
            strSqlOne.Append(",");
            strSqlOne.Append("CoName=@CoName");
            strSqlOne.Append(",");
            strSqlOne.Append("CoMobile=@CoMobile");
            strSqlOne.Append(",");
            strSqlOne.Append("CoQq=@CoQq");
            strSqlOne.Append(",");
            strSqlOne.Append("CoPhone=@CoPhone");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSummary=@CoSummary");
            strSqlOne.Append(",");
            strSqlOne.Append("CoIdentNumber=@CoIdentNumber");
            strSqlOne.Append(",");
            strSqlOne.Append("CoOtherTreatment=@CoOtherTreatment");
            strSqlOne.Append(",");
            strSqlOne.Append("CoWorkHistory=@CoWorkHistory");
            strSqlOne.Append(",");
            strSqlOne.Append("CoWorkType=@CoWorkType");
            strSqlOne.Append(",");
            strSqlOne.Append("CoSex=@CoSex");
            strSqlOne.Append(" where CoCandidatesId=@id;");
            SQLiteParameter[] parametersOne =
                {
                    new SQLiteParameter("@CoPost", DbType.String, 255),
                    new SQLiteParameter("@CoWorkExperience", DbType.String),
                    new SQLiteParameter("@CoEducation", DbType.String, 255),
                    new SQLiteParameter("@CoAge", DbType.String),
                    new SQLiteParameter("@CoDate", DbType.DateTime),
                    new SQLiteParameter("@CoLanguage", DbType.String, 255),
                    new SQLiteParameter("@CoEmail", DbType.String, 255),
                    new SQLiteParameter("@CoName", DbType.String, 255),
                    new SQLiteParameter("@CoMobile",  DbType.String, 255),
                    new SQLiteParameter("@CoQq",  DbType.String, 255),
                    new SQLiteParameter("@CoPhone", DbType.String, 255),
                    new SQLiteParameter("@CoSummary", DbType.String),
                    new SQLiteParameter("@CoIdentNumber", DbType.String, 255),
                    new SQLiteParameter("@CoWorkHistory", DbType.String),
                    new SQLiteParameter("@id", DbType.Int32)  ,
                    new SQLiteParameter("@CoSex", DbType.String,5)
                };
            parametersOne[0].Value = post;
            parametersOne[1].Value = workExperience;
            parametersOne[2].Value = education;
            parametersOne[3].Value = age;
            parametersOne[4].Value = date;
            parametersOne[5].Value = language;
            parametersOne[6].Value = email;
            parametersOne[7].Value = name;
            parametersOne[8].Value = mobile;
            parametersOne[9].Value = qq;
            parametersOne[10].Value = phone;
            parametersOne[11].Value = summary;
            parametersOne[12].Value = identNumber;
            parametersOne[13].Value = workHistory;
            parametersOne[14].Value = id;
            parametersOne[15].Value = sex;
            var obj = DbHelperSqLite.ExecuteSql(strSqlOne.ToString(), parametersOne).SafeToInt();
            return obj;

        }
        public int SetIsRead(int id, bool isRead)
        {
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("update TbCandidates set ");
            strSqlOne.Append("CoIsRead=@CoIsRead");
            strSqlOne.Append(" where CoCandidatesId=@id;");
            SQLiteParameter[] parametersOne =
                {
                    new SQLiteParameter("@id", DbType.Int32)  ,
                    new SQLiteParameter("@CoIsRead", DbType.Boolean)
                };
            parametersOne[0].Value = id;
            parametersOne[1].Value = isRead;
            var obj = DbHelperSqLite.ExecuteSql(strSqlOne.ToString(), parametersOne).SafeToInt();
            if (obj < 1)
            {
                throw new Exception("记录不存在");
            }
            return obj;
        }
        public void Delete(int id)
        {
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("delete  from TbCandidates  where CoCandidatesId=@id;");
            var parameterOne = new[] { new SQLiteParameter("@id", DbType.Int32) };
            parameterOne[0].Value = id;
            DbHelperSqLite.ExecuteSql(strSqlOne.ToString(), parameterOne);


        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string post, string mobile, string phone,string isRead, int pageValue, int rowsValue)
        {
            var parOne = new[]
                             {
                                 new SQLiteParameter("@CoPost", DbType.String, 255),
                                 new SQLiteParameter("@CoPhone", DbType.String, 255),
                                 new SQLiteParameter("@CoMobile", DbType.String, 255),
                                 new SQLiteParameter("@CoIsRead", DbType.Boolean)
                             };
            parOne[0].Value = post.SqliteLike();
            parOne[1].Value = mobile.SqliteLike();
            parOne[2].Value = phone.SqliteLike();
            parOne[3].Value = isRead.SafeToBool();
            var strWhere = new StringBuilder();
            strWhere.Append(" where  CoPost like '%'||@CoPost||'%' escape '\\'  ");
            strWhere.Append(" and  CoPhone like '%'||@CoPhone||'%' escape '\\'  ");
            strWhere.Append(" and  CoMobile like '%'||@CoMobile||'%' escape '\\'  ");
            if (!isRead.IsNullOrEmpty())
            {
                strWhere.Append(" and  CoIsRead = @CoIsRead  ");
            }
            var sqlOne = new StringBuilder();
            sqlOne.Append("select count(*) from TbCandidates ");
            sqlOne.Append(strWhere);
            int total = DbHelperSqLite.GetSingle(sqlOne.ToString(), parOne).SafeToInt();

            var parTwo = new[]
                             {

                                 new SQLiteParameter("@startIndex", DbType.Int32),
                                 new SQLiteParameter("@endIndex", DbType.Int32),
                                 new SQLiteParameter("@CoPost", DbType.String, 255),
                                 new SQLiteParameter("@CoPhone", DbType.String, 255),
                                 new SQLiteParameter("@CoMobile", DbType.String, 255),
                                 new SQLiteParameter("@CoIsRead", DbType.Int32)
                             };
            parTwo[0].Value = pageValue > 0 ? (pageValue - 1) * rowsValue : 0;
            parTwo[1].Value = rowsValue;
            parTwo[2].Value = post;
            parTwo[3].Value = mobile;
            parTwo[4].Value = phone;
            parTwo[5].Value = isRead.SafeToBool();
            var sqlTwo = new StringBuilder();
            sqlTwo.Append("select * from TbCandidates ");
            sqlTwo.Append(strWhere);
            sqlTwo.Append(" order by cocandidatesid  ");
            sqlTwo.Append(" LIMIT @endIndex OFFSET @startIndex ");
            var ds = DbHelperSqLite.Query(sqlTwo.ToString(), parTwo);
            var table = new Table
               {
                   Rows = ds.Tables["ds"].Rows,
                   Total = total
               };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            SQLiteParameter[] parameters =
                {
                    new SQLiteParameter("@id", DbType.Int32)
                };
            parameters[0].Value = id;
            var row = DbHelperSqLite.Query("select * from TbCandidates where CoCandidatesId=@id", parameters).Tables["ds"].Rows[0];
            return row;
        }
    }
}
